<!DOCTYPE html>


<html lang="zh-CN">


<head>
  <meta charset="utf-8" />
    
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
  <title>
    MySQL 表管理 |  
  </title>
  <meta name="generator" content="hexo-theme-ayer">
  
  <link rel="shortcut icon" href="/assets/img/favicon.ico" />
  
  <link rel="stylesheet" href="/dist/main.css">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/Shen-Yu/cdn/css/remixicon.min.css">
  <link rel="stylesheet" href="/css/custom.css">
  
  

  
<script>
var _hmt = _hmt || [];
(function() {
	var hm = document.createElement("script");
	hm.src = "https://hm.baidu.com/hm.js?e6614c2d092d0bce54a2543d8cf949fa";
	var s = document.getElementsByTagName("script")[0]; 
	s.parentNode.insertBefore(hm, s);
})();
</script>


<link rel="alternate" href="/atom.xml" title="null" type="application/atom+xml">
</head>

</html>

<body>
  <div id="app">
    
      
    <main class="content on">
      <section class="outer">
  <article
  id="post-MySQL-表管理"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h1 class="article-title sea-center" style="border-left:0" itemprop="name">
  MySQL 表管理
</h1>
 

    </header>
     
    <div class="article-meta">
      <a href="/2018/12/11/MySQL-表管理/" class="article-date">
  <time datetime="2018-12-11T09:02:32.000Z" itemprop="datePublished">2018-12-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/技术/">技术</a>
  </div>
  
<div class="word_count">
    <span class="post-time">
        <span class="post-meta-item-icon">
            <i class="ri-quill-pen-line"></i>
            <span class="post-meta-item-text"> 字数统计:</span>
            <span class="post-count">1.3k</span>
        </span>
    </span>

    <span class="post-time">
        &nbsp; | &nbsp;
        <span class="post-meta-item-icon">
            <i class="ri-book-open-line"></i>
            <span class="post-meta-item-text"> 阅读时长≈</span>
            <span class="post-count">5 分钟</span>
        </span>
    </span>
</div>
 
    </div>
      
    <div class="tocbot"></div>




  
    <div class="article-entry" itemprop="articleBody">
       
  <p>MySQL 其实就是一个包含很多表的集合，表中主要的数据类型可以分为整型、浮点型、字符类型和日期时间类型。MySQL 中数值类型可以指定是否区分正负和用零填补。选择数据类型时应符合最小、最合适的原则。</p>
<a id="more"></a>
<h2 id="整型"><a href="#整型" class="headerlink" title="整型"></a>整型</h2><p>MySQL 中整型类型有 tinyint、smallint、mediumint、int 和 bigint，分别占 1、2、3、4 和 8 个字节，其中 int 和 tinyint 最常用。</p>
<p>数据表中经常使用 id 字段作为主键，一般选择 int 类型，int unsigned 数值范围可达 42 亿，一般情况下足够业务上的应用。</p>
<p>需要注意的是，int(4) 和 int(11) 都占 4 个字节的空间，括号中的数字表示显示宽度，而非所占的字节数。如果类型定义为 int(n) zerofill，不足 n 位时会用 0 补充。</p>
<h2 id="浮点型"><a href="#浮点型" class="headerlink" title="浮点型"></a>浮点型</h2><p>浮点型包含 float、double 和 decimal，其中 float 占 4 个字节；double 占 8 个字节；decimal(m,d) 中 m 指最大位数（精度），范围是 1 到 65，d 是小数点右边的位数（刻度），范围为 0 到 30，且不得大于 m，d 省略时默认值为0，m 省略时默认值为10。</p>
<h2 id="时间类型"><a href="#时间类型" class="headerlink" title="时间类型"></a>时间类型</h2><p>时间类型包含 date、time、year、datetime 和 timestamp。</p>
<ul>
<li>date 占 3 个字节，范围为 1000-01-01 至 9999-12-31。</li>
<li>time 占 3 个字节，范围为 -838:59:59 至 838:59:59。</li>
<li>year 占 1 个字节，范围为 1901 至 2155。</li>
<li>datetime 在 5.6 之前占 8 个字节，5.6 之后占 5 个字节，范围为 1000-01-01 00:00:00 至 9999-12-31 23:59:59。</li>
<li>timestamp 占 4 个字节，范围为 1970-01-01 00:00:00 至 2038 年。</li>
</ul>
<p>datatime 可用范围比 timestamp 大，仅比 timestamp 多占 1 个字节的空间，生产环境可以优先使用 datetime。或者也可以使用 int 来存储时间，通过 unix_timestamp 和 from_timestamp 函数转换使用。</p>
<p>datetime 和 timestamp 在 5.6 之后均支持自动更新为当前时间 ON UPDATE CURRENT_TIMESTAMP。</p>
<h2 id="字符串类型"><a href="#字符串类型" class="headerlink" title="字符串类型"></a>字符串类型</h2><p>字符串类型包含 char、varchar、tinyblob、tinytext、blob、text、mediumblob、mediumtext、longblob 和 longtext。</p>
<ul>
<li>char 用于定长字符串，最多可容纳 255 个字符，没达到定义位数时尾部用空格不全存入表中，超过时截断。</li>
<li>varchar 用于变长字符串，最多占 65535 个字节，没达到定义位数不会补空格，超过时截断。</li>
</ul>
<blockquote>
<p>text、blob（二进制形式长文本）这种存储大量文字或图片的大数据类型最好不要与业务表放在一起。</p>
<p>不确定字段需要存储多少字符时使用  varchar 可以节约磁盘空间，提高存储效率。</p>
<p>varchar(100) 中 100 表示最大字符数，UTF8 字符集下存储空间 为 100 * 3 + 2 个字节。字节数小于 255 时用 1 个字节记录长度，超过 255 时用 2 个字节记录长度。</p>
<p>存储 IPv4 时可以使用 int 类型，通过 inet_aton 和 inet_ntoa 函数来转换。</p>
</blockquote>
<h2 id="字符集"><a href="#字符集" class="headerlink" title="字符集"></a>字符集</h2><p>MySQL 中字符集包括 character 字符集 和 collation 校对规则，字符集用来定义数据字符串的存储方式，校对规则定义比较字符串的方式。</p>
<p>常见字符集包括 GBK、Latin1、UTF8、UTF8mb6：Latin1 目前已经不适用了；GBK 一个字符占 2 个字节，通用性没有 UTF8 好；UTF8 一个字符占 3 个字节；UTF8mb4 是 UTF8 的超集，一个字符占 4 个字节，可以存储 emoji 符号。建议使用 UTF8mb4。</p>
<blockquote>
<p>临时修改数据库字符集，可以在数据库命令执行 set names，如 set names utf8。</p>
</blockquote>
<h2 id="碎片"><a href="#碎片" class="headerlink" title="碎片"></a>碎片</h2><p>在表中删除大量的数据后，数据文件的大小可能并没有减小。</p>
<p>delete 删除数据，MySQL 不会把数据文件真实删除，而是将数据文件的标示位删除，也不会整理数据文件，所以不会释放空间。新数据写入时，MySQL 会再次利用这些区域，但无法彻底占用。</p>
<p>删除操作会产生数据碎片，碎片会占用磁盘空间，扫描全表时也会扫描碎片部分，并且在读取效率方面比正常的空间低很多。</p>
<p>通过 <code>show table status like &#39;%table_name%&#39;</code> 语句可以查看表的状态，碎片大小 = 数据总大小 - 实际表空间文件大小，数据总大小 = data_length + index_length，实际表空间文件大小 = rows * avg_row_length。</p>
<p>innodb 引擎的表可以通过 <code>alter table table_name engine = innodb</code> 来重新整理全表数据，缺点是需要给整个表加写锁，并且需要一些时间。备份原表数据，删除整个表，重新导入到新表中也可以清理掉碎片。</p>
<h2 id="库表常用命令"><a href="#库表常用命令" class="headerlink" title="库表常用命令"></a>库表常用命令</h2><ul>
<li><code>use database</code> 选择数据库</li>
<li><code>show databases</code> 查看全部数据库</li>
<li><code>show tables</code> 查看当前库所有的表</li>
<li><code>create database database_name</code> 创建数据库</li>
<li><code>drop database database_name</code> 删除数据库</li>
<li><code>create table table_name (字段列表)</code> 创建表</li>
<li><code>drop table table_name</code> 删除表</li>
<li><code>delete from table_name (where)</code> 、<code>truncate table table_name</code> 删除表内数据</li>
<li><code>insert into table_name (字段列表) values (对应字段的值)</code> 插入数据</li>
<li><code>update table_name set 字段名 = 值 (where)</code> 更新表内数据</li>
<li><code>select * from table_name (where)</code> 查看表内数据</li>
<li><code>show create table table_name</code> 查看建表语句</li>
<li><code>desc table_name</code> 、<code>describe table_name</code> 查看表结构</li>
<li><code>show table status</code> 获取表基础信息</li>
<li><code>show index from table_name</code> 查看当前表索引</li>
<li><code>show full processlist</code> 查看数据库当前连接</li>
</ul>
 
      <!-- reward -->
      
      <div id="reword-out">
        <div id="reward-btn">
          打赏
        </div>
      </div>
      
    </div>
    

    <!-- copyright -->
    
    <div class="declare">
      <ul class="post-copyright">
        <li>
          <i class="ri-copyright-line"></i>
          <strong>版权声明： </strong>
          
          本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！
          
        </li>
      </ul>
    </div>
    
    <footer class="article-footer">
       
<div class="share-btn">
      <span class="share-sns share-outer">
        <i class="ri-share-forward-line"></i>
        分享
      </span>
      <div class="share-wrap">
        <i class="arrow"></i>
        <div class="share-icons">
          
          <a class="weibo share-sns" href="javascript:;" data-type="weibo">
            <i class="ri-weibo-fill"></i>
          </a>
          <a class="weixin share-sns wxFab" href="javascript:;" data-type="weixin">
            <i class="ri-wechat-fill"></i>
          </a>
          <a class="qq share-sns" href="javascript:;" data-type="qq">
            <i class="ri-qq-fill"></i>
          </a>
          <a class="douban share-sns" href="javascript:;" data-type="douban">
            <i class="ri-douban-line"></i>
          </a>
          <!-- <a class="qzone share-sns" href="javascript:;" data-type="qzone">
            <i class="icon icon-qzone"></i>
          </a> -->
          
          <a class="facebook share-sns" href="javascript:;" data-type="facebook">
            <i class="ri-facebook-circle-fill"></i>
          </a>
          <a class="twitter share-sns" href="javascript:;" data-type="twitter">
            <i class="ri-twitter-fill"></i>
          </a>
          <a class="google share-sns" href="javascript:;" data-type="google">
            <i class="ri-google-fill"></i>
          </a>
        </div>
      </div>
</div>

<div class="wx-share-modal">
    <a class="modal-close" href="javascript:;"><i class="ri-close-circle-line"></i></a>
    <p>扫一扫，分享到微信</p>
    <div class="wx-qrcode">
      <img src="//api.qrserver.com/v1/create-qr-code/?size=150x150&data=https://blog.ulyssesss.com/2018/12/11/MySQL-表管理/" alt="微信分享二维码">
    </div>
</div>

<div id="share-mask"></div>  
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/MySQL/">MySQL</a></li></ul>

    </footer>
  </div>

   
  <nav class="article-nav">
    
      <a href="/2018/12/17/MySQL-索引/" class="article-nav-link">
        <strong class="article-nav-caption">上一篇</strong>
        <div class="article-nav-title">
          
            MySQL 索引
          
        </div>
      </a>
    
    
      <a href="/2018/12/03/MySQL-数据库文件/" class="article-nav-link">
        <strong class="article-nav-caption">下一篇</strong>
        <div class="article-nav-title">MySQL 数据库文件</div>
      </a>
    
  </nav>

   
 
   
     
</article>

</section>
      <footer class="footer">
  <div class="outer">
    <ul>
      <li>
        Copyrights &copy;
        2016-2021
        <i class="ri-heart-fill heart_icon"></i> 大雪碧
      </li>
    </ul>
    <ul>
      <li>
        
      </li>
    </ul>
    <ul>
      <li>
        
      </li>
    </ul>
    <ul>
      
    </ul>
    <ul>
      
    </ul>
    <ul>
      <li>
        <!-- cnzz统计 -->
        
      </li>
    </ul>
  </div>
</footer>
      <div class="float_btns">
        <div class="totop" id="totop">
  <i class="ri-arrow-up-line"></i>
</div>

<div class="todark" id="todark">
  <i class="ri-moon-line"></i>
</div>

      </div>
    </main>
    <aside class="sidebar on">
      <button class="navbar-toggle"></button>
<nav class="navbar">
  
  <div class="logo">
    <a href="/"><img src="/assets/img/head.svg" alt=""></a>
  </div>
  
  <ul class="nav nav-main">
    
    <li class="nav-item">
      <a class="nav-item-link" href="/">主页</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/archives">归档</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/categories">分类</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/tags">标签</a>
    </li>
    
  </ul>
</nav>
<nav class="navbar navbar-bottom">
  <ul class="nav">
    <li class="nav-item">
      
      
    </li>
  </ul>
</nav>
<div class="search-form-wrap">
  <div class="local-search local-search-plugin">
  <input type="search" id="local-search-input" class="local-search-input" placeholder="Search...">
  <div id="local-search-result" class="local-search-result"></div>
</div>
</div>
    </aside>
    <script>
      if (window.matchMedia("(max-width: 768px)").matches) {
        document.querySelector('.content').classList.remove('on');
        document.querySelector('.sidebar').classList.remove('on');
      }
    </script>
    <div id="mask"></div>

<!-- #reward -->
<div id="reward">
  <span class="close"><i class="ri-close-line"></i></span>
  <p class="reward-p"><i class="ri-cup-line"></i>谢谢老板</p>
  <div class="reward-box">
    
    <div class="reward-item">
      <img class="reward-img" src="/assets/img/alipay.jpg">
      <span class="reward-type">支付宝</span>
    </div>
    
    
    <div class="reward-item">
      <img class="reward-img" src="/assets/img/wechat.jpg">
      <span class="reward-type">微信</span>
    </div>
    
  </div>
</div>
    <script src="/js/jquery-2.0.3.min.js"></script>
<script src="/js/lazyload.min.js"></script>
<!-- Tocbot -->

<script src="/js/tocbot.min.js"></script>
<script>
  tocbot.init({
    tocSelector: '.tocbot',
    contentSelector: '.article-entry',
    headingSelector: 'h1, h2, h3, h4, h5, h6',
    hasInnerContainers: true,
    scrollSmooth: true,
    scrollContainer: 'main',
    positionFixedSelector: '.tocbot',
    positionFixedClass: 'is-position-fixed',
    fixedSidebarOffset: 'auto'
  });
</script>

<script src="https://cdn.jsdelivr.net/npm/jquery-modal@0.9.2/jquery.modal.min.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jquery-modal@0.9.2/jquery.modal.min.css">
<script src="https://cdn.jsdelivr.net/npm/justifiedGallery@3.7.0/dist/js/jquery.justifiedGallery.min.js"></script>
<script src="/dist/main.js"></script>
<!-- ImageViewer -->

<!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">

    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>

    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">

        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>

        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">

            <div class="pswp__top-bar">

                <!--  Controls are self-explanatory. Order can be changed. -->

                <div class="pswp__counter"></div>

                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>

                <button class="pswp__button pswp__button--share" style="display:none" title="Share"></button>

                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>

                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>

                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                        <div class="pswp__preloader__cut">
                            <div class="pswp__preloader__donut"></div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div>
            </div>

            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>

            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>

            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>

        </div>

    </div>

</div>

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe.min.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/default-skin/default-skin.min.css">
<script src="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe-ui-default.min.js"></script>

<script>
    function viewer_init() {
        let pswpElement = document.querySelectorAll('.pswp')[0];
        let $imgArr = document.querySelectorAll(('.article-entry img:not(.reward-img)'))

        $imgArr.forEach(($em, i) => {
            $em.onclick = () => {
                // slider展开状态
                // todo: 这样不好，后面改成状态
                if (document.querySelector('.left-col.show')) return
                let items = []
                $imgArr.forEach(($em2, i2) => {
                    let img = $em2.getAttribute('data-idx', i2)
                    let src = $em2.getAttribute('data-target') || $em2.getAttribute('src')
                    let title = $em2.getAttribute('alt')
                    // 获得原图尺寸
                    const image = new Image()
                    image.src = src
                    items.push({
                        src: src,
                        w: image.width || $em2.width,
                        h: image.height || $em2.height,
                        title: title
                    })
                })
                var gallery = new PhotoSwipe(pswpElement, PhotoSwipeUI_Default, items, {
                    index: parseInt(i)
                });
                gallery.init()
            }
        })
    }
    viewer_init()
</script>

<!-- MathJax -->

<script type="text/x-mathjax-config">
  MathJax.Hub.Config({
      tex2jax: {
          inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
          processEscapes: true,
          skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
      }
  });

  MathJax.Hub.Queue(function() {
      var all = MathJax.Hub.getAllJax(), i;
      for(i=0; i < all.length; i += 1) {
          all[i].SourceElement().parentNode.className += ' has-jax';
      }
  });
</script>

<script src="https://cdn.jsdelivr.net/npm/mathjax@2.7.6/unpacked/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
<script>
  var ayerConfig = {
    mathjax: true
  }
</script>

<!-- Katex -->

<!-- busuanzi  -->

<!-- ClickLove -->

<!-- ClickBoom1 -->

<!-- ClickBoom2 -->

<!-- CodeCopy -->

<link rel="stylesheet" href="/css/clipboard.css">
<script src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js"></script>
<script>
  function wait(callback, seconds) {
    var timelag = null;
    timelag = window.setTimeout(callback, seconds);
  }
  !function (e, t, a) {
    var initCopyCode = function(){
      var copyHtml = '';
      copyHtml += '<button class="btn-copy" data-clipboard-snippet="">';
      copyHtml += '<i class="ri-file-copy-2-line"></i><span>COPY</span>';
      copyHtml += '</button>';
      $(".highlight .code pre").before(copyHtml);
      $(".article pre code").before(copyHtml);
      var clipboard = new ClipboardJS('.btn-copy', {
        target: function(trigger) {
          return trigger.nextElementSibling;
        }
      });
      clipboard.on('success', function(e) {
        let $btn = $(e.trigger);
        $btn.addClass('copied');
        let $icon = $($btn.find('i'));
        $icon.removeClass('ri-file-copy-2-line');
        $icon.addClass('ri-checkbox-circle-line');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPIED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('ri-checkbox-circle-line');
          $icon.addClass('ri-file-copy-2-line');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
      clipboard.on('error', function(e) {
        e.clearSelection();
        let $btn = $(e.trigger);
        $btn.addClass('copy-failed');
        let $icon = $($btn.find('i'));
        $icon.removeClass('ri-file-copy-2-line');
        $icon.addClass('ri-time-line');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPY FAILED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('ri-time-line');
          $icon.addClass('ri-file-copy-2-line');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
    }
    initCopyCode();
  }(window, document);
</script>


<!-- CanvasBackground -->


    
  </div>
</body>

</html>